﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using bdxyit.com.RecruitmentAppSys.IDAL;
using System.Data.SqlClient;
using bdxyit.com.RecruitmentAppSys.DBUtility;
using System.Data;
using bdxyit.com.RecruitmentAppSys.Models;

namespace bdxyit.com.RecruitmentAppSys.SQLDAL
{
    public class SkillLevelIdentificationInfoDAL : ISkillLevelIdentificationInfoDAL
    {
        private const string SQL_SELECT_SKILLLEVELIDENTIFICATIONINFO = " select  ID,StartTime,EndTime,SkillTypeID,SKillLevelID,SkillNum,UserID from SkillLevelIdentificationInfos ";

        private const string SQL_SELECT_SKILLLEVELIDENTIFICATIONINFO_BY_UserID = " select  top 1 ID,StartTime,EndTime,SkillTypeID,SKillLevelID,SkillNum,UserID from SkillLevelIdentificationInfos "
                                                                               + "  where UserID=@UserID";

        private const string SQL_DELETE_SKILLLEVELIDENTIFICATIONINFO_BY_UserID = " delete from SkillLevelIdentificationInfos "
                                                                               + "  where UserID=@UserID";

        private const string SQL_UPDATE_SKILLLEVELIDENTIFICATIONINFO_BY_UserID = " update SkillLevelIdentificationInfos set "
                                                                               + " StartTime=@StartTime,"
                                                                               + " EndTime=@EndTime,"
                                                                               + " SkillTypeID=@SkillTypeID,"
                                                                               + " SKillLevelID=@SKillLevelID,"
                                                                               + " SkillNum=@SkillNum,"
                                                                               + " UserID=@UserID"
                                                                               + "  where UserID=@UserID";


        private const string SQL_INSERT_SKILLLEVELIDENTIFICATIONINFO = " insert into SkillLevelIdentificationInfos("
                                                                     + " ID,StartTime,EndTime,SkillTypeID,SKillLevelID,SkillNum,UserID)"
                                                                     + "  values ("
                                                                     + " @ID,@StartTime,@EndTime,@SkillTypeID,@SKillLevelID,@SkillNum,@UserID)";




        private const string FIELD_ID = "@ID";
        private const string FIELD_StartTime = "@StartTime";
        private const string FIELD_EndTime = "@EndTime";
        private const string FIELD_SkillTypeID = "@SkillTypeID";
        private const string FIELD_SKillLevelID = "@SKillLevelID";
        private const string FIELD_SkillNum = "@SkillNum";
        private const string FIELD_UserID = "@UserID";



        /// <summary>
        /// 删除一条记录根据UserID
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public bool DeleteSkillLevelIdentificationInfoByUserID(string UserID)
        {
            bool flag = false;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 50);
            parm.Value = UserID;
            SqlCommand cmd = new SqlCommand();
            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_DELETE_SKILLLEVELIDENTIFICATIONINFO_BY_UserID;
                cmd.Parameters.Add(parm);
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 插入一条记录
        /// </summary>
        /// <param name="sil"></param>
        /// <returns></returns>
        public bool InsertSkillLevelIdentificationInfo(SkillLevelIdentificationInfo sil)
        {
            bool flag = false;

            SqlParameter[] SkillLevelIdentificationInfoParms = GetSkillLevelIdentificationInfoParameters();
            SqlCommand cmd = new SqlCommand();
            SkillLevelIdentificationInfoParms[0].Value = sil.ID;
            SkillLevelIdentificationInfoParms[1].Value = sil.StartTime;
            SkillLevelIdentificationInfoParms[2].Value = sil.EndTime;
            SkillLevelIdentificationInfoParms[3].Value = sil.SkillTypeID;
            SkillLevelIdentificationInfoParms[4].Value = sil.SKillLevelID;
            SkillLevelIdentificationInfoParms[5].Value = sil.SkillNum;
            SkillLevelIdentificationInfoParms[6].Value = sil.UserID;
            foreach (SqlParameter parm in SkillLevelIdentificationInfoParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_INSERT_SKILLLEVELIDENTIFICATIONINFO;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 更新数据根据UserID
        /// </summary>
        /// <param name="sil"></param>
        /// <returns></returns>
        public bool UpdateSkillLevelIdentificationInfo(SkillLevelIdentificationInfo sil)
        {
            bool flag = false;
            SqlParameter[] SkillLevelIdentificationInfoParms = GetSkillLevelIdentificationInfoParameters();
            SqlCommand cmd = new SqlCommand();
            SkillLevelIdentificationInfoParms[0].Value = sil.ID;
            SkillLevelIdentificationInfoParms[1].Value = sil.StartTime;
            SkillLevelIdentificationInfoParms[2].Value = sil.EndTime;
            SkillLevelIdentificationInfoParms[3].Value = sil.SkillTypeID;
            SkillLevelIdentificationInfoParms[4].Value = sil.SKillLevelID;
            SkillLevelIdentificationInfoParms[5].Value = sil.SkillNum;
            SkillLevelIdentificationInfoParms[6].Value = sil.UserID;

            foreach (SqlParameter parm in SkillLevelIdentificationInfoParms)
                cmd.Parameters.Add(parm);

            using (SqlConnection conn = new SqlConnection(SqlHelper.ConnectionStringLocal))
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = SQL_UPDATE_SKILLLEVELIDENTIFICATIONINFO_BY_UserID;
                cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                flag = true;
            }
            return flag;
        }

        /// <summary>
        /// 根据UserID查询一条记录
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public SkillLevelIdentificationInfo SelectSkillLevelIdentificationInfoByUserID(string UserID)
        {
            SkillLevelIdentificationInfo SkillLevelIdentificationInfo = null;
            //创建参数
            SqlParameter parm = new SqlParameter(FIELD_UserID, SqlDbType.VarChar, 50);
            parm.Value = UserID;

           
            using (SqlDataReader rdr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_SKILLLEVELIDENTIFICATIONINFO_BY_UserID, parm))
            {
                if (rdr.Read())
                {
                    SkillLevelIdentificationInfo = new SkillLevelIdentificationInfo();
                    if (rdr.HasRows == true)
                    {
                        if (rdr["ID"] != null && rdr["ID"].ToString() != "")
                        {
                            SkillLevelIdentificationInfo.ID = int.Parse(rdr["ID"].ToString());
                        }
                        if (rdr["StartTime"] != null && rdr["StartTime"].ToString() != "")
                        {
                            SkillLevelIdentificationInfo.StartTime = DateTime.Parse(rdr["StartTime"].ToString());
                        }
                        if (rdr["EndTime"] != null && rdr["EndTime"].ToString() != "")
                        {
                            SkillLevelIdentificationInfo.EndTime = DateTime.Parse(rdr["EndTime"].ToString());
                        }
                        if (rdr["SkillTypeID"] != null && rdr["SkillTypeID"].ToString() != "")
                        {
                            SkillLevelIdentificationInfo.SkillTypeID = rdr["SkillTypeID"].ToString();
                        }
                        if (rdr["SKillLevelID"] != null && rdr["SKillLevelID"].ToString() != "")
                        {
                            SkillLevelIdentificationInfo.SKillLevelID = rdr["SKillLevelID"].ToString();
                        }
                        if (rdr["SkillNum"] != null && rdr["SkillNum"].ToString() != "")
                        {
                            SkillLevelIdentificationInfo.SkillNum = rdr["SkillNum"].ToString();
                        }
                        if (rdr["UserID"] != null && rdr["UserID"].ToString() != "")
                        {
                            SkillLevelIdentificationInfo.UserID = rdr["UserID"].ToString();
                        }


                    }
                }
            }
            return SkillLevelIdentificationInfo;
        }

        /// <summary>
        /// 查询所有数据
        /// </summary>
        /// <returns></returns>
        public List<SkillLevelIdentificationInfo> SelectSkillLevelIdentificationInfo()
        {
            List<SkillLevelIdentificationInfo> L_skl = new List<SkillLevelIdentificationInfo>();
            using (SqlDataReader dr = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocal, CommandType.Text, SQL_SELECT_SKILLLEVELIDENTIFICATIONINFO))
            {
                while (dr.Read())
                {
                    SkillLevelIdentificationInfo skl = new SkillLevelIdentificationInfo();
                    if (dr["ID"] != null && dr["ID"].ToString() != "")
                    {
                        skl.ID = int.Parse(dr["ID"].ToString());
                    }
                    if (dr["StartTime"] != null && dr["StartTime"].ToString() != "")
                    {
                        skl.StartTime = DateTime.Parse(dr["StartTime"].ToString());
                    }
                    if (dr["EndTime"] != null && dr["EndTime"].ToString() != "")
                    {
                        skl.EndTime = DateTime.Parse(dr["EndTime"].ToString());
                    }
                    if (dr["SkillTypeID"] != null && dr["SkillTypeID"].ToString() != "")
                    {
                        skl.SkillTypeID = dr["SkillTypeID"].ToString();
                    }
                    if (dr["SKillLevelID"] != null && dr["SKillLevelID"].ToString() != "")
                    {
                        skl.SKillLevelID = dr["SKillLevelID"].ToString();
                    }
                    if (dr["SkillNum"] != null && dr["SkillNum"].ToString() != "")
                    {
                        skl.SkillNum = dr["SkillNum"].ToString();
                    }
                    if (dr["UserID"] != null && dr["UserID"].ToString() != "")
                    {
                        skl.UserID = dr["UserID"].ToString();
                    }
                    L_skl.Add(skl);
                }
            }
            return L_skl;
        }


        private SqlParameter[] GetSkillLevelIdentificationInfoParameters()
        {
            SqlParameter[] parms = new SqlParameter[] {
	                new SqlParameter(FIELD_ID, SqlDbType.Int,4),
					new SqlParameter(FIELD_StartTime, SqlDbType.DateTime),
					new SqlParameter(FIELD_EndTime, SqlDbType.DateTime),
					new SqlParameter(FIELD_SkillTypeID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_SKillLevelID, SqlDbType.VarChar,10),
					new SqlParameter(FIELD_SkillNum, SqlDbType.VarChar,100),
					new SqlParameter(FIELD_UserID, SqlDbType.VarChar,50)};
            return parms;
        }

    }
}